package com.sxj.corejava.code13_JDBC;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class Test04_练习 {

    public static void main(String[] args) throws DataAccessException {
//        Student student = new Student("zj200501", "admin", 20);
//        Student student2 = new Student("zj200502", "jack", 22);
//        Student student3 = new Student("zj200503", "lucy", 18);
//        Student student4 = new Student("zj200504", "alice", 21);
//        Student student5 = new Student("zj200505", "tom", 19);
//        insertStudent(student);
//        insertStudent(student2);
//        insertStudent(student3);
//        insertStudent(student4);
//        insertStudent(student5);
//        deleteByNo("zj200505");

//        Student student = new Student("zj200504", "tom", 19);
//        updateByNo(student);
//        List<Student> students = selectAll();
//        for(Student student : students){
//            System.out.println(student);
//        }

//        Student student = selectByNo("zj200501");
//        System.out.println(student);

//        List<Student> students = selectByName("a");
        List<Student> students = selectByAge(18, 21);
        for (Student student : students) {
            System.out.println(student);
        }
    }

    /**
     * 添加学生信息
     * @param student
     */
    public static void insertStudent(Student student) throws DataAccessException {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtil.getConnection();
            String sql = new StringBuffer()
                    .append(" insert into ")
                    .append(" t_student (no,name,age)")
                    .append(" values(?,?,?)")
                    .toString();
            ps = conn.prepareStatement(sql);
            ps.setString(1, student.getNo());
            ps.setString(2, student.getName());
            ps.setInt(3, student.getAge());
            ps.executeUpdate();
            System.out.println("添加成功");
        } catch (DataAccessException e) {
            throw e;
        } catch (SQLException e) {
            throw new DataAccessException(e);
        }

    }

    /**
     * 根据学号删除指定学生信息
     * @param no
     */
    public static void deleteByNo(String no) throws DataAccessException {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtil.getConnection();
            String sql = "delete from t_student where no = ? ";
            ps = conn.prepareStatement(sql);
            ps.setString(1, no);
            ps.executeUpdate();
            System.out.println("删除成功");
        } catch (DataAccessException e) {
            throw e;
        } catch (SQLException e) {
            throw new DataAccessException(e);
        }
    }

    /**
     * 根据学号修改指定学生信息
     * @param student
     */
    public static void updateByNo(Student student) throws DataAccessException {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtil.getConnection();
            String sql = new StringBuffer()
                    .append(" update t_student")
                    .append(" set ")
                    .append("   name = ?,")
                    .append("   age = ?")
                    .append(" where no = ?")
                    .toString();
            ps = conn.prepareStatement(sql);
            ps.setString(1, student.getName());
            ps.setInt(2, student.getAge());
            ps.setString(3, student.getNo());
            ps.executeUpdate();
            System.out.println("提示:恭喜您,修改成功!");
        } catch (DataAccessException e) {
            throw e;
        } catch (SQLException e) {
            throw new DataAccessException(e);
        } finally {
            JDBCUtil.close(conn, ps, null);
        }
    }

    /**
     * 查询所有学生信息
     * @return
     * @throws DataAccessException
     */
    public static List<Student> selectAll() throws DataAccessException {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Student> students = new ArrayList<>();
        try {
            conn = JDBCUtil.getConnection();
            String sql = new StringBuffer()
                    .append(" select id,name,no,age ")
                    .append(" from t_student ")
                    .toString();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                Student student = new Student();
                student.setId(rs.getInt("id"));
                student.setNo(rs.getString("no"));
                student.setName(rs.getString("name"));
                student.setAge(rs.getInt("age"));
                students.add(student);
            }
        } catch (DataAccessException e) {
            throw e;
        } catch (SQLException e) {
            throw new DataAccessException(e);
        } finally {
            JDBCUtil.close(conn, ps, rs);
        }
        return students;
    }

    /**
     * 根据学号查询指定学生信息
     * @param no
     */
    public static Student selectByNo(String no) throws DataAccessException {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Student student = null;
        try {
            conn = JDBCUtil.getConnection();
            String sql = new StringBuffer()
                    .append(" select id,name,no,age ")
                    .append(" from t_student ")
                    .append(" where no = ? ")
                    .toString();
            ps = conn.prepareStatement(sql);
            ps.setString(1, no);
            rs = ps.executeQuery();
            if (rs.next()) {
                student = new Student();
                student.setId(rs.getInt("id"));
                student.setNo(rs.getString("no"));
                student.setName(rs.getString("name"));
                student.setAge(rs.getInt("age"));
            }
        } catch (DataAccessException e) {
            throw e;
        } catch (SQLException e) {
            throw new DataAccessException(e);
        } finally {
            JDBCUtil.close(conn, ps, rs);
        }
        return student;
    }

    /**
     * 根据姓名进行模糊查询
     * @param name
     */
    public static List<Student> selectByName(String name) throws DataAccessException {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Student> students = new ArrayList<>();
        try {
            conn = JDBCUtil.getConnection();
            String sql = new StringBuffer()
                    .append(" select id,name,no,age ")
                    .append(" from t_student ")
                    .append(" where name like ? ")
                    .toString();
            ps = conn.prepareStatement(sql);
            ps.setString(1, "%" + name + "%");
            rs = ps.executeQuery();
            while (rs.next()) {
                Student student = new Student();
                student.setId(rs.getInt("id"));
                student.setNo(rs.getString("no"));
                student.setName(rs.getString("name"));
                student.setAge(rs.getInt("age"));
                students.add(student);
            }
        } catch (DataAccessException e) {
            throw e;
        } catch (SQLException e) {
            throw new DataAccessException(e);
        } finally {
            JDBCUtil.close(conn, ps, rs);
        }
        return students;
    }

    /**
     * 根据年龄范围查询指定学生信息
     * @param minAge
     * @param maxAge
     */
    public static List<Student> selectByAge(int minAge, int maxAge) throws DataAccessException {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Student> students = new ArrayList<>();
        try {
            conn = JDBCUtil.getConnection();
            String sql = new StringBuffer()
                    .append(" select id,no,name,age ")
                    .append(" from t_student")
                    .append(" where age between ? and ? ")
                    .toString();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, minAge);
            ps.setInt(2, maxAge);
            rs = ps.executeQuery();
            while (rs.next()) {
                Student student = new Student();
                student.setId(rs.getInt("id"));
                student.setNo(rs.getString("no"));
                student.setName(rs.getString("name"));
                student.setAge(rs.getInt("age"));
                students.add(student);
            }
        } catch (DataAccessException e) {
            throw e;
        } catch (SQLException e) {
            throw new DataAccessException(e);
        } finally {
            JDBCUtil.close(conn, ps, rs);
        }
        return students;
    }


}
